package com.sellerSystem.dao.Impl;

import java.util.ArrayList;
import java.util.List;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;

import org.springframework.stereotype.Repository;

import com.sellerSystem.dao.SellerDao;
import com.sellerSystem.vo.Commodity;
import com.sellerSystem.vo.ComplaintInfo;
import com.sellerSystem.vo.Demo;
import com.sellerSystem.vo.OrderForm;
import com.sellerSystem.vo.Seller;
import com.sellerSystem.vo.Temp;

@Repository("sd")
public class SellerDaoImpl implements SellerDao {
	@PersistenceContext(name = "un")
	private EntityManager em;

	// 卖家注册
	@Override
	public int addSeller(Seller seller) {
		// TODO Auto-generated method stub
		em.persist(seller);
		return seller.getSellerId();
	}

	// 卖家登录
	@SuppressWarnings("unchecked")
	@Override
	public Seller findSeller(String sellerTel, String sellerPsd) {
		// TODO Auto-generated method stub
		String jpql = "select s from Seller s where s.sellerTel=:sellerTel and s.sellerPsd=:sellerPsd";
		List<Seller> ls = em.createQuery(jpql)
				.setParameter("sellerTel", sellerTel)
				.setParameter("sellerPsd", sellerPsd).getResultList();

		// System.out.println(ls.isEmpty()+"!!!!!!!!!!");
		if (ls.isEmpty())
			return null;
		else
			return ls.get(0);
	}

	// 验证Seller电话是否存在
	@SuppressWarnings("unchecked")
	@Override
	public Boolean findTel(String sellerTel) {
		// TODO Auto-generated method stub
		String jpql = "select s from Seller s where s.sellerTel=:sellerTel";
		List<Seller> s = em.createQuery(jpql)
				.setParameter("sellerTel", sellerTel).getResultList();
		if (s.isEmpty())
			return false;
		else
			return true;
	}

	// 修改密码
	@Override
	public int modifyPsd(String sellerTel, String sellerPsd) {
		// TODO Auto-generated method stub
		String jpql = "update Seller s set s.sellerPsd=:sellerPsd where s.sellerTel=:sellerTel";
		int rs = em.createQuery(jpql).setParameter("sellerTel", sellerTel)
				.setParameter("sellerPsd", sellerPsd).executeUpdate();
		if (rs == 0)
			return 0;
		else
			return rs;

	}

	// 添加商品
	@Override
	public int addCommodity(Commodity commodity) {
		// TODO Auto-generated method stub
		em.persist(commodity);
		return commodity.getCommodityId();
	}

	// 修改个人信息
	@Override
	public int modifyInfo(Seller seller) {
		// TODO Auto-generated method stub

		String jpql = "update Seller s set "
				+ "s.sellerTel=:sellerTel,s.sellerEmail=:sellerEmail,"
				+ "s.shoppingStyle=:shoppingStyle,s.sellerQQ=:sellerQQ,"
				+ "s.shoppingName=:shoppingName,s.sellerAddress.detailAddress=:detailAddress,"
				+ "s.sellerAddress.province=:province,s.sellerAddress.city=:city,"
				+ "s.sellerAddress.town=:town,s.shoppingTel=:shoppingTel,"
				+ "s.sellerRemark=:sellerRemark where s.sellerName=:sellerName";
		em.createQuery(jpql)
				.setParameter("sellerName", seller.getSellerName())
				.setParameter("sellerTel", seller.getSellerTel())
				.setParameter("sellerEmail", seller.getSellerEmail())
				.setParameter("shoppingStyle", seller.getShoppingStyle())
				.setParameter("sellerQQ", seller.getSellerQQ())
				.setParameter("shoppingName", seller.getShoppingName())
				.setParameter("detailAddress",
						seller.getSellerAddress().getDetailAddress())
				.setParameter("province",
						seller.getSellerAddress().getProvince())
				.setParameter("city", seller.getSellerAddress().getCity())
				.setParameter("town", seller.getSellerAddress().getTown())
				.setParameter("shoppingTel", seller.getShoppingTel())
				.setParameter("sellerRemark", seller.getSellerRemark())
				.executeUpdate();

		/*
		 * String jpql = "delete from Seller s where s.sellerName=:sellerName";
		 * em.createQuery(jpql).setParameter("sellerName",
		 * seller.getSellerName()) .executeUpdate(); em.persist(seller);
		 */
		return 1;
	}

	// 修改商品
	@Override
	public int modifyCommodity(Commodity commodity) {
		// TODO Auto-generated method stub

		String jpql = "update Commodity sp set sp.commodityId=:commodityId,"
				+ "sp.commodityName=:commodityName,sp.commodityPic=:commodityPic,"
				+ "sp.commodityPrice=:commodityPrice,sp.commodityKinds=:commodityKinds,"
				+ "sp.commodityGuaranty=:commodityGuaranty,sp.commodityNumber=:commodityNumber,"
				+ "sp.commodityDescribe=:commodityDescribe where sp.commodityId=:commodityId";
		em.createQuery(jpql)
				.setParameter("commodityId", commodity.getCommodityId())
				.setParameter("commodityName", commodity.getCommodityName())
				.setParameter("commodityPic", commodity.getCommodityPic())
				.setParameter("commodityPrice", commodity.getCommodityPrice())
				.setParameter("commodityKinds", commodity.getCommodityKinds())
				.setParameter("commodityGuaranty",
						commodity.getCommodityGuaranty())
				.setParameter("commodityNumber", commodity.getCommodityNumber())
				.setParameter("commodityDescribe",
						commodity.getCommodityDescribe()).executeUpdate();
		// .getResultList();
		return commodity.getCommodityId();
	}

	// 删除商品
	@Override
	public int deleteCommodity(Commodity commodity) {
		String jpql = "delete from Commodity c where c.commodityId=:commodityId";
		int rs = em.createQuery(jpql)
				.setParameter("commodityId", commodity.getCommodityId())
				.executeUpdate();
		return rs;
	}

	// 查询商品
	@Override
	public Commodity queryCommodity(Integer commodityId) {
		// TODO Auto-generated method stub
		String jpql = "select c from Commodity c where c.commodityId=:commodityId";
		@SuppressWarnings("unchecked")
		List<Commodity> lc = em.createQuery(jpql)
				.setParameter("commodityId", commodityId).getResultList();
		return lc.get(0);
	}

	// 查看评价
	@Override
	public void checkEvaluation() {
		// TODO Auto-generated method stub

	}

	// 查询商家所有商品
	@SuppressWarnings("unchecked")
	@Override
	public ArrayList<Commodity> findCommodity(String sellerName) {
		// TODO Auto-generated method stub
		String jpql = "select c from Commodity c where c.sellerName=:sellerName";
		ArrayList<Commodity> lc = (ArrayList<Commodity>) em.createQuery(jpql)
				.setParameter("sellerName", sellerName).getResultList();
		return lc;

	}

	// 查询所有商家
	@SuppressWarnings("unchecked")
	@Override
	public ArrayList<Seller> seller(Integer sellerState) {
		// TODO Auto-generated method stub
		String jpql = "select s from Seller s where sellerState=:sellerState";
		ArrayList<Seller> ls = (ArrayList<Seller>) em.createQuery(jpql)
				.setParameter("sellerState", sellerState).getResultList();
		return ls;
	}

	// 查询单个商家
	@SuppressWarnings("unchecked")
	@Override
	public Seller findId(int sellerId) {
		// TODO Auto-generated method stub
		String jpql = "select s from Seller s where s.sellerId=:sellerId";
		List<Seller> ls = (List<Seller>) em.createQuery(jpql)
				.setParameter("sellerId", sellerId).getResultList();
		return ls.get(0);
	}

	// 查询所有账单
	@Override
	public List<OrderForm> allbill() {
		// TODO Auto-generated method stub
		String jpql = "select o from OrderForm o ";
		List<OrderForm> lo = (List<OrderForm>) em.createQuery(jpql)
				.getResultList();
		return lo;
	}

	// 查询一段时间账单
	@Override
	public List<OrderForm> billbyid(String sellerName, String time1,
			String time2) {
		String jpql = "select o from OrderForm o where o.orderTime > :time1 and o.orderTime < :time2 and o.sellerName=:sellerName";
		List<OrderForm> lo = em.createQuery(jpql).setParameter("time1", time1)
				.setParameter("time2", time2)
				.setParameter("sellerName", sellerName).getResultList();
		if (lo.isEmpty()) {
			return null;
		} else {
			return lo;
		}
	}

	// 查询个人账单
	public List<OrderForm> billName(String sellerName) {
		String jpql = "select o from OrderForm o where o.sellerName=:sellerName";

		System.out.println(jpql);
		List<OrderForm> lo = em.createQuery(jpql)
				.setParameter("sellerName", sellerName).getResultList();
		if (lo.isEmpty()) {
			return null;
		} else {
			return lo;
		}
	}
	// 查询个人账单
	public List<Demo> billName1(String sellerName) {
		String jpql = "select new com.sellerSystem.vo.Demo(orderId, orderNumber,orderTime,commodityId,sellerName,customerId,receiver,receiverTel,money,orderStatus,evaluation,orderTip) from OrderForm o where o.sellerName=:sellerName";

		
		System.out.println(jpql);
		List<Demo> lo = em.createQuery(jpql)
				.setParameter("sellerName", sellerName).getResultList();
		if (lo.isEmpty()) {
			return null;
		} else {
			return lo;
		}
	}

	// 修改卖家状态
	@Override
	public int modifystate(Integer sellerId) {
		// TODO Auto-generated method stub
		String jpql = "update Seller s set s.sellerState=1 where s.sellerId=:sellerId";
		int rs = em.createQuery(jpql).setParameter("sellerId", sellerId)
				.executeUpdate();
		return rs;
	}

	// 审核不通过
	@Override
	public int approvalfail(Integer sellerId, String approvalOpinion) {
		String jpql = "update Seller s set s.sellerState=2 ,s.approvalOpinion=:approvalOpinion where s.sellerId=:sellerId";
		int rs = em.createQuery(jpql).setParameter("sellerId", sellerId)
				.setParameter("approvalOpinion", approvalOpinion)
				.executeUpdate();
		return rs;
	}

	// 加入黑名单
	@Override
	public int joinblack(Integer sellerId, String sellerRemark) {
		// TODO Auto-generated method stub
		String jpql = "update Seller s set s.sellerState=3, s.sellerRemark=:sellerRemark where s.sellerId=:sellerId";
		int rs = em.createQuery(jpql).setParameter("sellerId", sellerId)
				.setParameter("sellerRemark", sellerRemark).executeUpdate();
		return rs;
	}

	// 查询对某一卖家的投诉信息
	@Override
	public List<ComplaintInfo> findComplaintInfo(Integer sellerId) {
		String jpql = "select c from ComplaintInfo c join c.seller s where s.sellerId=:sellerId ";
		List<ComplaintInfo> complaintInfo = em.createQuery(jpql)
				.setParameter("sellerId", sellerId).getResultList();
		if (complaintInfo.isEmpty()) {
			return null;
		} else {
			return complaintInfo;
		}
	}

	// 查询卖家的投诉信息统计
	@Override
	public List<Seller> countComplaintInfo() {
		// "select c.sellerId, count(c) from ComplaintInfo c join c.seller s group by s.sellerId";
		String jpql = "select s from Seller s where s.complaintNum > 20 and s.sellerState=1 ";
		List<Seller> list = em.createQuery(jpql).getResultList();
		if (list.isEmpty()) {
			return null;
		}
		return list;
	}

	// 黑名单候选人
	@Override
	public List<Seller> ifBlacklist() {
		// TODO Auto-generated method stub
		String jpql = "select s from Seller s where s.complaintNum > 20 and s.sellerState=1 ";
		List<Seller> ls = em.createQuery(jpql).getResultList();
		return ls;
	}

	// 不拉黑
	@Override
	public int noblack(Integer sellerId) {
		// TODO Auto-generated method stub
		String jpql = "update Seller s set s.sellerState=1,s.complaintNum=10 where s.sellerId=:sellerId";
		em.createQuery(jpql).setParameter("sellerId", sellerId).executeUpdate();
		return sellerId;
	}

	// 查询所有投诉信息
	@Override
	public List<ComplaintInfo> complaintInfo() {
		// TODO Auto-generated method stub
		String jpql = "select c from ComplaintInfo c join c.seller s";
		List<ComplaintInfo> lc = em.createQuery(jpql).getResultList();

		return lc;
	}

	// 修改订单状态
	@Override
	public int modifyofstate(OrderForm orderForm) {
		// TODO Auto-generated method stub
		String jpql = "update OrderForm o set o.orderStatus=:orderStatus where o.orderId=:orderId";
		em.createQuery(jpql).setParameter("orderId", orderForm.getOrderId())
				.setParameter("orderStatus", orderForm.getOrderStatus())
				.executeUpdate();
		return orderForm.getOrderId();
	}

	// 反馈投诉信息
	@Override
	public int handlecomplaint(Integer complaintInfoId, Integer complaintStatu,
			String complaintFeedback) {
		// TODO Auto-generated method stub
		String jpql = "update ComplaintInfo c set c.complaintStatu=:complaintStatu,c.complaintFeedback=:complaintFeedback where c.complaintInfoId=:complaintInfoId";
		int rs = em.createQuery(jpql)
				.setParameter("complaintInfoId", complaintInfoId)
				.setParameter("complaintStatu", complaintStatu)
				.setParameter("complaintFeedback", complaintFeedback)
				.executeUpdate();
		return rs;
	}

	// 单个卖家日账单查询
	@Override
	public List<Temp> findBillBySellerIdAndDay(String sellerName) {
		// TODO Auto-generated method stub
		String jpql = "select new com.sellerSystem.vo.Temp(date(o.orderTime) as date,sum(o.money)as money) from OrderForm o  where sellerName = :sellerName GROUP BY date( o.orderTime) order by date( o.orderTime) ";
		System.out.println(jpql);
		List<Temp> temps = em.createQuery(jpql)
				.setParameter("sellerName", sellerName).getResultList();
		if (temps.isEmpty()) {
			return null;
		} else {
			return temps;
		}

	}

	// 单个卖家月账单查询
	@Override
	public List<Temp> findBillBySellerIdAndMonth(String sellerName) {
		// TODO Auto-generated method stub
		String jpql = "select new com.sellerSystem.vo.Temp(YEAR(o.orderTime) as year,Month(o.orderTime) as month,sum(o.money)as money) from OrderForm o where sellerName = :sellerName GROUP BY YEAR( o.orderTime), Month( o.orderTime)  ";
		List<Temp> temps = em.createQuery(jpql)
				.setParameter("sellerName", sellerName).getResultList();
		if (temps.isEmpty()) {
			return null;
		} else {
			return temps;
		}
	}

	// 单个卖家年账单查询
	@Override
	public List<Temp> findBillBySellerIdAndYear(String sellerName) {
		// TODO Auto-generated method stub
		String jpql = "select new com.sellerSystem.vo.Temp(YEAR(o.orderTime) as year,sum(o.money)as money) from OrderForm o where sellerName = :sellerName GROUP BY YEAR( o.orderTime) order by YEAR( o.orderTime)";
		List<Temp> temps = em.createQuery(jpql)
				.setParameter("sellerName", sellerName).getResultList();
		if (temps.isEmpty()) {
			return null;
		} else {
			return temps;
		}

	}

	// 商场日账单查询
	@Override
	public List<Temp> findBillByDay() {
		// TODO Auto-generated method stub
		String jpql = "select new com.sellerSystem.vo.Temp(date(o.orderTime) as date,sum(o.money)as money) from OrderForm o  GROUP BY date( o.orderTime) order by date( o.orderTime)";
		System.out.println(jpql);
		List<Temp> temps = em.createQuery(jpql).getResultList();
		if (temps.isEmpty()) {
			return null;
		} else {
			return temps;
		}

	}

	// 商场月账单查询
	@Override
	public List<Temp> findBillByMonth() {
		// String jpql =
		// "select new com.sellerSystem.vo.Temp(YEAR(o.orderTime) as YEAR,MONTH(o.orderTime) as MONTH,sum(o.money)as money) from OrderForm o  GROUP BY YEAR(o.orderTime) as YEAR,MONTH( o.orderTime) as MONTH ";
		String jpql = "select new com.sellerSystem.vo.Temp(YEAR(o.orderTime) as year,Month(o.orderTime) as month,sum(o.money)as money) from OrderForm o GROUP BY YEAR( o.orderTime), Month( o.orderTime)  ";

		// String jpql =
		// "select new com.sellerSystem.vo.Temp(YEAR(o.orderTime) as YEAR,MONTH(o.orderTime) as MONTH,DATE_FORMAT(FROM_UNIXTIME(date(o.orderTime)),'%Y-%m') ,sum(o.money)as money) from OrderForm o   GROUP BY DATE_FORMAT(FROM_UNIXTIME(date(o.orderTime)),'%Y-%m')  ";
		List<Temp> temps = em.createQuery(jpql).getResultList();
		if (temps.isEmpty()) {
			return null;
		} else {
			return temps;
		}
	}

	// 商场年账单查询
	@Override
	public List<Temp> findBillByYear() {
		// TODO Auto-generated method stub
		String jpql = "select new com.sellerSystem.vo.Temp(YEAR(o.orderTime) as year,sum(o.money)as money) from OrderForm o  GROUP BY YEAR( o.orderTime) order by YEAR( o.orderTime)";
		List<Temp> temps = em.createQuery(jpql).getResultList();
		if (temps.isEmpty()) {
			return null;
		} else {
			return temps;
		}

	}

}
